昨天，我們花了一整天，把一則廣播的回覆搞成了一棵樹。

最後大概長這樣：

broadcasts
----------
id
author_id
content
created_at
ack_deadline_at

replies
-------
id
broadcast_id
author_id
content
ref_id
created_at

其中 ref_id 指向另一筆 Reply。

如果是 NULL，代表直接回覆 Broadcast；如果有值，就代表它是在回覆另一則 Reply。

於是，一棵樹就長出來了。

所以今天要幹嘛？

昨天不是說要把它種進 PostgreSQL？

對。

但真的準備開始寫的時候，我才發現——

昨天好像少畫了幾個東西。

你昨天不是畫得很開心嗎？

咳。

畫 Schema 跟真的實作，果然還是兩回事。

廣播到底發給誰？

首先是最明顯的問題。

目前的 broadcasts 長這樣：

broadcasts
----------
id
author_id
content
created_at
ack_deadline_at

有作者、有內容、有時間。

但少了一個很重要的東西：

這則廣播到底發給誰？

前面在設計 Group 的時候，我們其實就已經決定，廣播的作用範圍不應該綁在 Account 上，而是綁在 Group。

例如：

高二
├── 398
├── 399
└── 400

真正收到廣播的是底下這些班級。

而同一則 Broadcast 可以發給很多個 Group，同一個 Group 當然也可以收到很多則 Broadcast。

所以這就是一個很標準的 Many-to-Many（多對多）關係。

那就補一張中介表：

broadcast_targets
-----------------
broadcast_id
group_id

PK (broadcast_id, group_id)

現在一則廣播就可以長成：

Broadcast #100
├── 398
├── 399
└── 400

broadcast_targets 只負責回答一件事：

這則廣播發給哪些 Group？

其他東西先不要往裡面塞。

那資訊股長確認了沒？

既然有了 Target，下一個問題自然就來了。

假設 Broadcast #100 發給：

398
399
400

那我們除了知道這三個班級應該收到廣播，也需要知道：

各班資訊股長到底確認了沒？

這件事不能直接塞進 broadcast_targets。

因為「這則廣播發給 399」跟「399 已經確認這則廣播」，其實是兩件不同的事情。

所以再拆一張：

broadcast_confirmations
-----------------------
broadcast_id
group_id
confirmed_by_account_id
confirmed_at

PK (broadcast_id, group_id)

這裡有個滿重要的差別。

確認責任是：

Broadcast × Group

也就是「399 班有沒有確認這則廣播」。

而不是：

Broadcast × Account

不然同一個班級只要換個 Account 登入，就可能莫名其妙多出另一份確認責任。

confirmed_by_account_id 的用途只是留下 Audit Trail（稽核軌跡）。

也就是：

399 班已經確認了這則廣播，而實際完成這次操作的是 Account X。

責任屬於班級的資訊股長，Account 則留下實際操作紀錄。

所以 (broadcast_id, group_id) 直接作為 Primary Key。

同一個班級對同一則 Broadcast，只會有一筆確認。

廣播什麼時候算今天的？

接著還有昨天的 ack_deadline_at。

昨天我們已經知道，這東西不是「廣播的死亡時間」。

廣播資料不會因為時間到了就消失。

它真正代表的是：

資訊股長這一輪最晚什麼時候要確認。

而這裡最後決定用一個固定的確認週期。

假設放學時間是：

16:30

那我們把今天的廣播收單時間放在放學前一個半小時：

15:00

因此：

14:59 發送
→ 歸入今天的確認週期
→ 16:30 前確認

15:00 發送
→ 歸入下一個確認週期
→ 下一個有效週期的 16:30 前確認

這樣就不會發生 16:20 才丟一則廣播，然後要求資訊股長在十分鐘內負責確認的奇怪情況。

那 15:00 之後的廣播就臭酸了嗎？

沒有。

只是它被放進下一輪而已。

15:00 是這一輪的收單時間，16:30 才是這一輪資訊股長的確認 Deadline。

所以 ack_deadline_at 也不需要用 NULL 表示「這則不用確認」。

每一則廣播都可以直接算出自己屬於哪一個確認週期：

broadcasts
----------
id
author_id
content
created_at
ack_deadline_at NOT NULL

至於假日、週末之類的問題……

欸。

今天先不要。

你終於學會收斂了。

謝謝。

所以最後長什麼樣？

整理一下，今天真正要種進 PostgreSQL 的東西就是這四張：

broadcasts
-------------------------
id
author_id
content
created_at
ack_deadline_at

broadcast_targets
-------------------------
broadcast_id
group_id

PK (broadcast_id, group_id)

broadcast_confirmations
-------------------------
broadcast_id
group_id
confirmed_by_account_id
confirmed_at

PK (broadcast_id, group_id)

replies
-------------------------
id
broadcast_id
author_id
content
ref_id
created_at

其中 Reply 還保留昨天設計的 Self-referencing Foreign Key（自我參照外鍵）：

replies.ref_id → replies.id

也就是昨天畫出來的那棵樹。

Foreign Key 不是萬能的

不過真的準備實作之後，還有一件事情滿有意思的。

昨天我們留下了一條規則：

reply.broadcast_id == ref.broadcast_id

如果 Reply B 說自己屬於 Broadcast #200，卻又透過 ref_id 回覆 Broadcast #100 底下的 Reply A，那整棵樹就接歪了。

但普通的 Foreign Key 只能保證：

ref_id 指向的 Reply 存在

它不會自動幫我們判斷：

你們兩個是不是屬於同一則 Broadcast

broadcast_confirmations 也有類似問題。

我們當然希望：

confirmation.group_id

真的存在於：

broadcast_targets

而不是 399 根本沒收到這則廣播，資料庫裡卻突然冒出一筆「399 已確認」。

這時候事情就開始從：

「Foreign Key 怎麼寫？」

變成：

「哪些規則應該讓 Database 保證，哪些規則應該交給 Service Layer？」

但再繼續研究下去，今天大概又會從「把 Schema 種進 PostgreSQL」，一路歪成「資料庫約束設計大全」。

所以——

先停。

目前的資料結構已經足夠讓我們開始實作了。

剩下的 Constraint（約束）要放在哪一層，等真的遇到問題再處理。

所以終於要寫 Code 了？

對。

這次真的。

接下來我把剛剛整理好的 Schema、關聯和確認週期規則交給 Agent，讓它按照目前專案既有的 SQLAlchemy Model 與 Alembic Migration 結構完成實作。

最後 Agent 幫我們補上 Model、Migration 和測試，Migration 也成功接上目前的 20260927_0004。
至於剛剛那兩條 Foreign Key 管不到的規則，我們暫時把它們留給之後的 Service Layer。
好，樹真的種下去了。